OpenEdge Data Management:<br />DataServer for ODBC Preparing to use the DataServer utilities

Subsequent sections contain tutorials that illustrate using the DataServer utilities to maintain your schema holder. Before you use these tutorials, you must perform some preparatory steps, including creating the demonstration database and schema holder and accessing the DataServer’s ODBC Utilities menu.

Make sure that you have done the following:

Installed and started your data source.

Installed and configured your drivers.

Installed and configured any client software required by your non-OpenEdge data source vendor.

Create an empty foreign data source.

Start the ODBC administration tool for your data source.

Select the appropriate ODBC driver.

Select the target ODBC data source.

Make any additional selections required by your ODBC data source.

Specify mysport as data source name. The name that you use to register a data source is the name that OpenEdge recognizes.

Exit the ODBC administration tool.

Make sure that the target ODBC data source has started.

Start a local OpenEdge session using the following command:


`install-path\bin\prowin32`

Starting a local OpenEdge session also automatically starts the local DataServer.

Access the Data Administration tool. Create a copy of the Sports database and connect to it.

Select DataServer

ODBC Utilities

Schema Migration Tools

Progress DB to ODBC to start the Progress-to-ODBC Utility.

Specify Progress-to-ODBC parameter values as noted in Table 5–1.

Table 5–1: Progress-to-ODBC utility (
Interface element	Description
Original PROGRESS Database	Accept the name of the connected source database or type the name of a database to which to connect.
Connect parameters for PROGRESS	If you did not specify a new value for the name of the Original PROGRESS Database parameter, do not modify the Connect parameters for PROGRESS Database parameter. If you did specify a new value, type any additional connect parameters that are necessary.
Name of Schema holder Database	Type the name of the schema holder. The utility creates the schema holder if it does not exist.
ODBC Data Source Name	Type the data source name (This is the name you specified when registering the data source in step 7 of the "Preparing to use the DataServer utilities" section). This is the name of the schema image and the name that you will use to refer to the target database in applications. The data-source name must be different from the name that you typed for the schema holder and different from the name of any other schema image existing in that schema holder.
Foreign DBMS Type	Select the foreign data-source type to which the ODBC driver is connecting. OpenEdge provides the following choices: Sybase, DB2/400, DB2 (Other), Informix, and Other. Note: If you select DB2/400, the Library field appears in this dialog box directly below the Collation name field. For a description of this field, see the Library field entry in this table. Select Other if you are accessing a target data source other than the ones listed here. Note that you will get only the generic ODBC SQL-92 functionality if you access databases other than the ones listed here.
ODBC Username	Type the user ID.
ODBC User’s Password	Type the password.
ODBC connect parameters	See Chapter 4, "Connecting the DataServer," for required connection parameters. For example, `-Dsrv` might be required in some cases.
Codepage for Schema Image	Accept the default code page.
Collation Name	Enter the Progress name for the collation that your ODBC data source will use. The default is Basic.
Library	Enter the library name associated with the DB2/400 data source. Defining the library ensures that when the schema is pushed across to the AS/400 machine, qualified SQL statements are generated. (If you don’t specify the library, the ODBC driver defaults the library name to the userid of the connected user.) This field only appears if you selected DB2/400 in the data source in the Foreign DBMS Type drop-down list earlier on this screen.
Progress 4GL Compatible Objects	Leave this toggle box checked to create an ODBC data source that supports arrays, backward and forward scrolling, and the Progress record identifier in data sources that will support this functionality. These objects result in the addition of columns to your foreign data-source tables. Uncheck this toggle box if you do not want the Extended 4GL capability.
Load SQL	Leave this toggle box checked.
Create Shadow Columns	If your ODBC data source is case sensitive, and you wish to maintain Progress-compatible, case insensitive behavior, check this box, otherwise leave blank.
Move Data	Check this toggle box to dump and load data from the OpenEdge database to the target database. Copying data from a large database can take a long time. You can uncheck this toggle box if you want to dump and load data at a more convenient time. This toggle box is available only if the Load SQL toggle box is checked.

Note: For a complete description of these Progress-to-ODBC parameters, see Table 5–5 in the "Running the Progress-to-ODBC utility" section.

Running the utility creates and connects a schema holder and the ODBC data source. It operates as follows:

SQL script is generated.

SQL that creates the schema is sent to the foreign data manager.

The schema is pulled back to the schema holder.

The foreign schema holder and the OpenEdge database are compared and all information needed by OpenEdge is applied to the schema holder.

If the Move Data toggle box was selected, the data is loaded.

The schema holder is disconnected.

A message is displayed that tells the user which startup procedure to use to connect.

Choose DataServer

ODBC Utilities to see the available DataServer utilities, described in Table 5–2.

Table 5–2: DataServer utilities
ODBC utility	Description
Create DataServer Schema...	Creates a schema image in the schema holder for an ODBC data source.
Update/Add Table Definitions...	Updates the schema holder to reflect any changes that you make to data-source data definitions.
Verify Table Definition...	Makes sure that the data definitions in the schema holder match your data-source data definitions.
Edit Connection Information...	Changes connection information or the logical database name for a data source.
Change DataServer Schema Code Page...	Changes the code page in the schema holder associated with the ODBC data source.
Delete DataServer Schema...	Deletes a schema image from the schema holder.
Schema Migration Tools	Accesses utilities for migrating or comparing an OpenEdge database to an ODBC data source.

When you access a DataServer utility (as you will do in the tutorials that follow this section), the following dialog box might appear before the utility opens:

In the User ID and Password dialog box, choose OK if you are satisfied with the user ID and password combination that you have already supplied. If you want to change them, or they were never specified, enter a user ID and password with the privileges required for creating and updating a schema holder. See the "Establishing permissions" section for more information on the required privileges.